package com.efast.dmom.mgmt.service.mes.plan.impl;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import com.efast.cafe.framework.bean.ResultBean;
import com.efast.cafe.framework.jdbc.JdbcDao;
import com.efast.dmom.mgmt.bean.mes.InfPpPlan;
import com.efast.dmom.mgmt.dao.mes.TwProductionPlanMapper;
import com.efast.dmom.mgmt.service.mes.plan.IPlanningSchedulingService;

@Service
public class PlanningSchedulingService implements IPlanningSchedulingService{
	@Autowired
	private TwProductionPlanMapper twProductionPlanMapper;
	
	@Autowired
	@Qualifier("jdbcDao")
	private JdbcDao jdbcDao;
	
	@SuppressWarnings("deprecation")
	private Date checkOnlineDate(InfPpPlan plan, String workshop, Date onlineDate) {
//		if(date.before(new Date())) {
//			return date;
//		}
//		DateFormat df1 = DateFormat.getDateInstance();
//		String sql ="SELECT jph  from mes_work_calendar where  workshop = '"+workshop+"' and calendar_date = '"+df1.format(date)+"' ";
//		sql+="and is_deleted=0 and status='Y' and activate=0 ";
//		List<Map<String, Object>> result = jdbcDao.queryForListMap(sql);
//		if(result!=null && !result.isEmpty()) {
//			return date;
//		}else {
//			date.setDate(date.getDate()-1);
//			return checkOnlineDate(date , workshop);
//		}
		
		if(onlineDate == null || workshop == null || "".equals(workshop)){
			return null;
		}
		
		if(onlineDate.after(new Date())){
			
			System.out.println("当前时间：" + new Date());
			
			SimpleDateFormat simpleDateFormat =new SimpleDateFormat ("yyyy-MM-dd");
			
			StringBuffer capacityQuery = new StringBuffer();
			capacityQuery.append("SELECT sum(csr.plan_input) as capacity from mes_work_calendar wc left join mes_calendar_shift_relation csr on "
					+ " wc.id = csr.calendar_id where  wc.workshop = '");
			capacityQuery.append(workshop+ "' and wc.calendar_date >= '");
			capacityQuery.append(simpleDateFormat.format(onlineDate));
			capacityQuery.append("' and wc.calendar_date <= '");
			capacityQuery.append(simpleDateFormat.format(plan.getPlanOfflineDate()));
			capacityQuery.append("' and wc.is_deleted=0 and csr.plan_input >0 and wc.activate=0");
			
			int configCapacity = -1;
			List<Map<String, Object>> configCapacityMap = jdbcDao.queryForListMap(capacityQuery.toString());
			if(configCapacityMap!=null && configCapacityMap.size()>0) {
				Map<String, Object> map = configCapacityMap.get(0);
				configCapacity = (map.get("capacity") == null ? 0 : Integer.valueOf(((Object)map.get("capacity")).toString()));
			}
	System.out.println("获取产能sql: " + capacityQuery.toString());
	System.out.println("计划上线时间：" + onlineDate + " 计划下线时间：" + plan.getPlanOfflineDate() + "  产能为：" + configCapacity);
			
			StringBuffer planQtyQuery = new StringBuffer();
			planQtyQuery.append("SELECT sum(qty) as capacity from inf_pp_plan  WHERE ");
			planQtyQuery.append("plan_offline_date <= '");
			planQtyQuery.append(simpleDateFormat.format(plan.getPlanOfflineDate()));
			planQtyQuery.append("' and plan_status not in (100, 10, 70)");
			
			int scheduledCapacity = -1;
			List<Map<String, Object>> scheduledCapacityMap = jdbcDao.queryForListMap(planQtyQuery.toString());
			if(scheduledCapacityMap!=null && scheduledCapacityMap.size()>0) {
				Map<String, Object> map = scheduledCapacityMap.get(0);
				scheduledCapacity = (map.get("capacity") == null ? 0 : Integer.valueOf(((Object)map.get("capacity")).toString()));
			}
	System.out.println("计划上线时间：" + onlineDate + " 计划下线时间：" + plan.getPlanOfflineDate() + "  计划量为：：" + scheduledCapacity);
			int planQty = (plan.getQty() == null)? 0 : plan.getQty();
			
			if(configCapacity < planQty + scheduledCapacity){
				onlineDate.setDate(onlineDate.getDate()-1);
	System.out.println("产能不够，往后前退一天： " + onlineDate);
				checkOnlineDate(plan, workshop, onlineDate);
			}
			else{
	System.out.println("产能够，返回当前时间： " + onlineDate);	
				return onlineDate;
			}
		}
	System.out.println("已排产到当前时间，返回当前时间： " + onlineDate);	
		return onlineDate;
	}
	
	@Override
	public Date getOnlineDate() {
		Date cDate = new Date();
		SimpleDateFormat simpleDateFormat =new SimpleDateFormat ("yyyy-MM-dd");
		//获取当前最大的上线日期
		Date date = twProductionPlanMapper.selectMaxOnlineDate();
		
		//上线日期为空或者上线日期小于当前日期
		if(date==null || date.before(cDate)) {
			StringBuilder sb = new StringBuilder();
			sb.append("SELECT calendar_date,sum(plan_input) ");
			sb.append("from mes_work_calendar ");
			sb.append("where calendar_date >=  SYSDATE() ");
			sb.append("and is_deleted=0 and plan_input >0 ");
			sb.append("and activate=0 and workshop = 'BS' ");
			sb.append("group by calendar_date ");
			sb.append("order by calendar_date asc ");
			sb.append("limit 1 ");
			final String sqlQuery = sb.toString();
			List<Map<String, Object>> queryForListMap = jdbcDao.queryForListMap(sqlQuery);
			if(queryForListMap!=null && queryForListMap.size()>0) {
				Map<String, Object> map = queryForListMap.get(0);
				Object dateMin = map.get("calendar_date");
				return (Date)dateMin;
			}
		}
		
		//查询当前的排产量
		String sqlCount = "SELECT sum(qty) as sum from inf_pp_plan  WHERE plan_online_date='"+simpleDateFormat.format(date)+"' ";
		
		List<Map<String, Object>> resultCount = jdbcDao.queryForListMap(sqlCount);
		int onlineCount = 0;
		if(resultCount!=null && resultCount.size()>0) {
			Object object = resultCount.get(0).get("sum");
			if(object!=null) {
				onlineCount = Integer.valueOf(object.toString());
			}
		}
		
		//查询当天的计划产量
		String sql ="SELECT sum(plan_input) as sum from mes_work_calendar where  workshop = 'BS' and calendar_date = '"+simpleDateFormat.format(date)+"' ";
		sql+="and is_deleted=0 and plan_input >0 and activate=0 ";
		List<Map<String, Object>> result = jdbcDao.queryForListMap(sql);
		int count =0;
		if(result!=null && result.size()>0) {
			Object object = result.get(0).get("sum");
			if(object!=null) {
				count =  Integer.valueOf(object.toString());
			}
		}
		//如果计划产线大于已拍产的量
		if(count > onlineCount) {
			return date;
		
		}else {
			StringBuilder sb = new StringBuilder();
			sb.append("SELECT calendar_date,sum(plan_input) ");
			sb.append("from mes_work_calendar ");
			sb.append("where calendar_date >=  '"+simpleDateFormat.format(date)+"' ");
			sb.append("and is_deleted=0 and plan_input >0 ");
			sb.append("and activate=0 and workshop = 'BS' ");
			sb.append("group by calendar_date ");
			sb.append("order by calendar_date asc ");
			sb.append("limit 1 ");
			final String sqlQuery = sb.toString();
			
			List<Map<String, Object>> queryForListMap = jdbcDao.queryForListMap(sqlQuery);
			
			if(queryForListMap!=null && queryForListMap.size()>0) {
				Map<String, Object> map = queryForListMap.get(0);
				Object dateMin = map.get("calendar_date");
				return (Date)dateMin;
			}
		}
			
		return null;
	}
	@Override
	public Date[] getOnlineDate(InfPpPlan plan) {
		//1获取总装上线日期
		Date asOnlineDate = getASOnlineDate(plan);
		if(asOnlineDate==null) {
			return new Date[] {null,null,null};
		}
		//2获取涂装上线时间
		Date psOnlineDate = getPSOnlineDate(plan, asOnlineDate);
		if(psOnlineDate==null) {
			return new Date[] {asOnlineDate,null,null};
		}
		//3获取焊装上线时间
		Date bsOnlineDate = getBSOnlineDate(plan, psOnlineDate);
		if(bsOnlineDate==null) {
			return new Date[] {asOnlineDate,psOnlineDate,null};
		}
		
		return new Date[] {asOnlineDate,psOnlineDate,bsOnlineDate};
		
		
	}
	@SuppressWarnings("deprecation")
	private Date getASOnlineDate(InfPpPlan plan) {
		SimpleDateFormat simpleDateFormat =new SimpleDateFormat ("yyyy-MM-dd");
		//1.获取总装上线
		//1.1.获取总装下线时间
		Date offlineDate=null;
		Date asOnlineDate = null; //总装上线时间
		String sql = "SELECT plan_offline_date from inf_pp_plan WHERE plan_order_no='"+plan.getPlanOrderNo()+"'";
		List<Map<String, Object>> queryForListMap = jdbcDao.queryForListMap(sql);
		if(queryForListMap!=null && !queryForListMap.isEmpty()) {
			Map<String, Object> map = queryForListMap.get(0);
			Object object = map.get("plan_offline_date");
			if(object!=null) {
				offlineDate = (Date)object;
			}
		}
		if(offlineDate==null) {
			return null;
		}
		//1.2计算总装上线时间
		//1.2.1查询下线当天是否生产
		boolean isWork = false;
		int jph =0;
		sql ="SELECT csr.jph as jph from mes_work_calendar wc left join mes_calendar_shift_relation csr on"
				+ " wc.id = csr.calendar_id where  wc.workshop = 'AS' and wc.calendar_date = '"+simpleDateFormat.format(offlineDate)+"' ";
		sql+="and wc.is_deleted=0 and wc.status='Y' and wc.activate=0 ";
		List<Map<String, Object>> result = jdbcDao.queryForListMap(sql);
		if(result!=null && !result.isEmpty()) {
			isWork=true;
			Map<String, Object> map = result.get(0);
			Object object = map.get("jph");
			if(object!=null) {
				jph = Integer.valueOf(object.toString());
			}
		}
		
		//1.2.2计算总装上线时间  workstations/jph (小时)
		int asStations  =0;
		sql = "SELECT param_value FROM portal_base_parameter  where param_key='AS_STATIONS'";
		result = jdbcDao.queryForListMap(sql);
		if(result!=null && !result.isEmpty()) {
			Map<String, Object> map = result.get(0);
			Object object = map.get("param_value");
			if(object!=null) {
				asStations = Integer.valueOf(object.toString());
			}
		}
		if(asStations==0) {
			return null;
		}
		
		if(isWork) {
			if(jph<=0) {
				return null;
			}
			int dayWorks = asStations/jph/8;
			asOnlineDate = new Date(offlineDate.getYear(),offlineDate.getMonth(),offlineDate.getDate());
			asOnlineDate.setDate(asOnlineDate.getDate()-dayWorks);
		}else {
			StringBuilder sb = new StringBuilder();
			sb.append("SELECT wc.calendar_date as calendar_date , csr.jph as jph ");
			sb.append("from mes_work_calendar wc left join mes_calendar_shift_relation csr on wc.id = csr.calendar_id ");
			sb.append("where wc.calendar_date > SYSDATE() and wc.calendar_date <  '"+simpleDateFormat.format(offlineDate)+"' ");
			sb.append("and wc.is_deleted=0 and csr.plan_input >0 ");
			sb.append("and wc.activate=0 and wc.workshop = 'AS' ");
			sb.append("group by wc.calendar_date ");
			sb.append("order by wc.calendar_date desc ");
			sb.append("limit 1 ");
			final String sqlQuery = sb.toString();
			queryForListMap = jdbcDao.queryForListMap(sqlQuery);
			if(queryForListMap!=null && !queryForListMap.isEmpty()) {
				Map<String, Object> map = queryForListMap.get(0);
				asOnlineDate = (Date)map.get("calendar_date");
				jph = (int)map.get("jph");
				if(jph<=0) {
					return null;
				}
				int dayWorks = asStations/jph/8;
				asOnlineDate.setDate(asOnlineDate.getDate()-dayWorks);
			}
			
		}
	System.out.println("对于计划：" + plan.getPlanOnlineDate() + " 获取总装上线时间：" + asOnlineDate);
		return checkOnlineDate(plan,"AS", asOnlineDate);
	}
	
	@SuppressWarnings("deprecation")
	private Date getPSOnlineDate(InfPpPlan plan, Date asOnlineDate) {
		SimpleDateFormat simpleDateFormat =new SimpleDateFormat ("yyyy-MM-dd");
		Date psOnlineDate = null;
		//1.2计算涂装上线时间
		//1.2.1查询总装上线当天涂装是否生产
		boolean isWork = false;
		int jph =0;
		String sql ="SELECT csr.jph as jph from mes_work_calendar wc left join mes_calendar_shift_relation csr on wc.id = csr.calendar_id "
				+ " where  wc.workshop = 'PS' and wc.calendar_date = '"+simpleDateFormat.format(asOnlineDate)+"' ";
		sql+=" and wc.is_deleted=0 and wc.status='Y' and wc.activate=0 ";
		List<Map<String, Object>> result = jdbcDao.queryForListMap(sql);
		if(result!=null && !result.isEmpty()) {
			isWork=true;
			Map<String, Object> map = result.get(0);
			Object object = map.get("jph");
			if(object!=null) {
				jph = Integer.valueOf(object.toString());
				
			}
		}
		
		//1.2.2计算涂装上线时间  workstations/jph (小时)
		int psStations  =0;
		sql = "SELECT param_value FROM portal_base_parameter  where param_key='PS_STATIONS'";
		result = jdbcDao.queryForListMap(sql);
		if(result!=null && !result.isEmpty()) {
			Map<String, Object> map = result.get(0);
			Object object = map.get("param_value");
			if(object!=null) {
				psStations = Integer.valueOf(object.toString());
			}
		}
		if(psStations==0) {
			return null;
		}
		
		if(isWork) {
			if(jph<=0) {
				return null;
			}
			int dayWorks = psStations/jph/8;
			psOnlineDate = new Date(asOnlineDate.getYear(),asOnlineDate.getMonth(),asOnlineDate.getDate());
			psOnlineDate.setDate(psOnlineDate.getDate()-dayWorks);
		}else {
			StringBuilder sb = new StringBuilder();
			sb.append("SELECT wc.calendar_date as calendar_date , csr.jph as jph ");
			sb.append("from mes_work_calendar wc left join mes_calendar_shift_relation csr on wc.id = csr.calendar_id ");
//			sb.append("where calendar_date <  '"+df1.format(asOnlineDate)+"' ");
			sb.append("where wc.calendar_date > SYSDATE() and wc.calendar_date <  '"+simpleDateFormat.format(asOnlineDate)+"' ");
			sb.append("and wc.is_deleted=0 and csr.plan_input >0 ");
			sb.append("and wc.activate=0 and wc.workshop = 'PS' ");
			sb.append("group by wc.calendar_date ");
			sb.append("order by wc.calendar_date desc ");
			sb.append("limit 1 ");
			final String sqlQuery = sb.toString();
			List<Map<String, Object>> queryForListMap = jdbcDao.queryForListMap(sqlQuery);
			if(queryForListMap!=null && !queryForListMap.isEmpty()) {
				Map<String, Object> map = queryForListMap.get(0);
				psOnlineDate = (Date)map.get("calendar_date");
				jph = (int)map.get("jph");
				if(jph<=0) {
					return null;
				}
				int dayWorks = psStations/jph/8;
				psOnlineDate.setDate(psOnlineDate.getDate()-dayWorks);
			}
			
		}
		return checkOnlineDate(plan,"PS",psOnlineDate);
	}
	
	@SuppressWarnings("deprecation")
	private Date getBSOnlineDate(InfPpPlan plan, Date psOnlineDate) {
		SimpleDateFormat simpleDateFormat =new SimpleDateFormat ("yyyy-MM-dd");
		Date bsOnlineDate = null;
		//1.2计算焊装上线时间
		//1.2.1查询涂装上线当天焊装装是否生产
		boolean isWork = false;
		int jph =0;
		String sql ="SELECT wc.calendar_date as calendar_date , csr.jph as jph from mes_work_calendar wc left join mes_calendar_shift_relation csr on wc.id = csr.calendar_id"
				+ " where wc.workshop = 'BS' and wc.calendar_date = '"+simpleDateFormat.format(psOnlineDate)+"' ";
		sql+="and wc.is_deleted=0 and wc.status='Y' and wc.activate=0 ";
		List<Map<String, Object>> result = jdbcDao.queryForListMap(sql);
		if(result!=null && !result.isEmpty()) {
			isWork=true;
			Map<String, Object> map = result.get(0);
			Object object = map.get("jph");
			if(object!=null) {
				jph = Integer.valueOf(object.toString());
				
			}
		}
		
		//1.2.2计算焊装上线时间  workstations/jph (小时)
		int bsStations  =0;
		sql = "SELECT param_value FROM portal_base_parameter  where param_key='BS_STATIONS'";
		result = jdbcDao.queryForListMap(sql);
		if(result!=null && !result.isEmpty()) {
			Map<String, Object> map = result.get(0);
			Object object = map.get("param_value");
			if(object!=null) {
				bsStations = Integer.valueOf(object.toString());
			}
		}
		if(bsStations==0) {
			return null;
		}
		
		if(isWork) {
			if(jph<=0) {
				return null;
			}
			int dayWorks = bsStations/jph/8;
			bsOnlineDate = new Date(psOnlineDate.getYear(),psOnlineDate.getMonth(),psOnlineDate.getDate());
			
			bsOnlineDate.setDate(bsOnlineDate.getDate()-dayWorks);
		}else {
			StringBuilder sb = new StringBuilder();
			sb.append("SELECT wc.calendar_date as calendar_date , csr.jph as jph ");
			sb.append("from mes_work_calendar wc left join mes_calendar_shift_relation csr on wc.id = csr.calendar_id  ");
//			sb.append("where calendar_date <  '"+df1.format(psOnlineDate)+"' ");
			sb.append("where wc.calendar_date > SYSDATE() and wc.calendar_date <  '"+simpleDateFormat.format(psOnlineDate)+"' ");
			sb.append("and wc.is_deleted=0 and csr.plan_input >0 ");
			sb.append("and wc.activate=0 and wc.workshop = 'BS' ");
			sb.append("group by wc.calendar_date ");
			sb.append("order by wc.calendar_date desc ");
			sb.append("limit 1 ");
			final String sqlQuery = sb.toString();
			List<Map<String, Object>> queryForListMap = jdbcDao.queryForListMap(sqlQuery);
			if(queryForListMap!=null && !queryForListMap.isEmpty()) {
				Map<String, Object> map = queryForListMap.get(0);
				bsOnlineDate = (Date)map.get("calendar_date");
				jph = (int)map.get("jph");
				if(jph<=0) {
					return null;
				}
				int dayWorks = bsStations/jph/8;
				bsOnlineDate.setDate(bsOnlineDate.getDate()-dayWorks);
			}
			
		}
		return checkOnlineDate(plan, "BS", bsOnlineDate);
	}
	
	@Override
	public ResultBean changeOnlineDate(String plan, Date[] date) {
		
		return setOnlineDate( plan,  date) ;
	}

	@Override
	public ResultBean setOnlineDate(String plan, Date[] date) {
		ResultBean resultBean = new ResultBean();
		Integer result = twProductionPlanMapper.updateOnlineDate(date, plan);
		if(result >0) {
			resultBean.setSuccess(true);
			resultBean.setMessage("更新成功，返回为"+result);
			return resultBean;
		}
		resultBean.setSuccess(false);
		resultBean.setMessage("更新失败，返回为"+result);
		return resultBean;
	}

	@Override
	public ResultBean autoSetOnlineDate(InfPpPlan plan) {
		
		ResultBean resultBean = new ResultBean();
		
		if(plan == null){
			resultBean.setSuccess(false);
			resultBean.setMessage("在系统中无此计划记录记录");
			return resultBean;
		}
		
		if(plan.getPlanOfflineDate() == null){
			resultBean.setSuccess(false);
			resultBean.setMessage("请维护计划【" + plan.getPlanOrderNo() + "】的计划下线时间");
			return resultBean;
		}
		
		Date offLineDate = plan.getPlanOfflineDate();
		if(new Date().after(offLineDate)){
			resultBean.setSuccess(false);
			resultBean.setMessage("计划【" + plan.getPlanOrderNo() + "】计划下线时间早于当前时间,请重新配置计划下线时间");
			return resultBean;
		}
		
		String  sql ="SELECT plan_order_no,plan_online_date from inf_pp_plan  WHERE plan_order_no='"+plan.getPlanOrderNo()+"'";
		List<Map<String, Object>> queryForListMap = jdbcDao.queryForListMap(sql);
		
		if(queryForListMap!=null && queryForListMap.size()>0) {
			Map<String, Object> map = queryForListMap.get(0);
			Object object = map.get("plan_online_date");
			if(object!=null) {
				resultBean.setSuccess(false);
				resultBean.setMessage(plan.getPlanOrderNo()+"已经排序，有上线日期");
				return resultBean;
			}
		}else {
			resultBean.setSuccess(false);
			resultBean.setMessage(plan.getPlanOrderNo()+"在系统中无记录");
			return resultBean;
		}
		
		Date[] onlineDate = getOnlineDate(plan);
		Date date = onlineDate[2];
		if(date==null) {
			resultBean.setSuccess(false);
			resultBean.setMessage(plan.getPlanOrderNo()+"在系统无法获取上线日期，请检查日历配置");
			return resultBean;
		}
		return setOnlineDate(plan.getPlanOrderNo(),onlineDate);
	}

	@Override
	public ResultBean autoSetOlineDate(List<InfPpPlan> plans) {
		ResultBean resultBean = new ResultBean();
		resultBean.setSuccess(true);
		resultBean.setMessage("");
		for(InfPpPlan plan:plans) {
			ResultBean autoSetOnlineDate = autoSetOnlineDate(plan);
			resultBean.setMessage(resultBean.getMessage()+";"+autoSetOnlineDate.getMessage());
			if(!autoSetOnlineDate.isSuccess()) {
				resultBean.setSuccess(false);
			}
		}
		return resultBean;
	}

	@Override
	public ResultBean querySchedulingDateResult(Date date) {
		ResultBean resultBean = new ResultBean();
		resultBean.setSuccess(true);
		resultBean.setMessage("");
		SimpleDateFormat simpleDateFormat =new SimpleDateFormat ("yyyy-MM-dd");

		//查询当前的排产量
		String sqlCount = "SELECT sum(qty) as sum from inf_pp_plan  WHERE plan_online_date='"+simpleDateFormat.format(date)+"' ";
		
		List<Map<String, Object>> resultCount = jdbcDao.queryForListMap(sqlCount);
		int onlineCount = 0;
		if(resultCount!=null && resultCount.size()>0) {
			Object object = resultCount.get(0).get("sum");
			if(object!=null) {
				onlineCount = Integer.valueOf(object.toString());
			}
		}
		
		//查询当天的计划产量
		String sql ="SELECT sum(plan_input) as sum from mes_work_calendar where  workshop = 'BS' and calendar_date = '"+simpleDateFormat.format(date)+"' ";
		sql+="and is_deleted=0 and plan_input >0 and activate=0 ";
		List<Map<String, Object>> result = jdbcDao.queryForListMap(sql);
		int count =0;
		if(result!=null && result.size()>0) {
			Object object = result.get(0).get("sum");
			if(object!=null) {
				count =  Integer.valueOf(object.toString());
			}
		}
		//如果计划产线大于已拍产的量
		if(count >= onlineCount) {
			resultBean.setSuccess(true);
			resultBean.setMessage("实际排产数量符合计划投入数量");
		
		}else {
			resultBean.setSuccess(false);
			resultBean.setMessage("实际排产数量大于计划投入数量");
			
		}
		
		return resultBean;
	}

	


}
